package OSMS.dao;

import java.util.ArrayList;
import java.util.List;

import OSMS.entity.Car;
import OSMS.entity.Customers;
import OSMS.entity.Goods;
import OSMS.entity.Order;
import OSMS.tools.JDBC_Util;

public class GoodsDao {
	/**
	 * 查询Goods表并返回List<Goods> 集合
	 * JDBC_Utils
	 */
	public List<Goods> searchGoodsByGid (int gid) throws Exception {
		String sql = "select * from goods where gid = ?" ;
		List<Goods> gList = JDBC_Util.queryBeanList(sql, Goods.class,gid);
		return gList ;
	}
	/**
	 * 查询Order表并返回List<Order> 集合     
	 * JDBC_Utils
	 *  7/9修改
	 */
	public List<Order> orderList(int cid) throws Exception {
		String sql = "select * from orderform where cid = ? order by buy_date desc";
		List<Order> orderList = JDBC_Util.queryBeanList(sql, Order.class, cid);
		return orderList ;
	}
	/**
	 * 根据用户编号查询购物车信息，返回Car对象
	 */
	public List<Car> getCarById(int cid) throws Exception {
		String sql = "select * from shoppingcar where cid = ?";
		List<Car> orderList = JDBC_Util.queryBeanList(sql, Car.class, cid);
		return orderList ;
	}
	/**
	 * 删除方法
	 * 删除购物车中的某件商品
	 */
	public boolean deleteGoods(int gid,int cid) throws Exception {
		String sql = "delete from shoppingcar where gid = ? and cid = ? ";
		boolean flag = JDBC_Util.updateDb(sql, gid ,cid);
		return flag;
	}
	/**
	 * 删除方法 结算后删除购物车中商品信息
	 */
	public boolean deleteCar(int cid) throws Exception {
		String sql = "delete from shoppingcar where cid = ? ";
		boolean flag = JDBC_Util.updateDb(sql,cid);
		return flag;
	}
	/**
	 * 新增方法 向订单表里插入数据
	 *  7/9号
	 */
	public boolean addOrder(int cid , ArrayList<Car> carList) {
		int cnt = 0;
		boolean flag = true;
		String sql = "insert into orderform (did ,cid , buy_date,good_num,gprice,gid,gname) values (null, ? ,current_timestamp,?,?,?,?,?)";
		for(int i = 0;i<carList.size()&&flag;i++)
		{
			flag = JDBC_Util.updateDb(sql , cid  , carList.get(i).getGood_num() , carList.get(i).getGprice() , carList.get(i).getGid(),carList.get(i).getGname(),carList.get(i).getPicture());
			cnt++;
		}
		if(cnt==carList.size())
			return true;
		return false ; 
	}
	
	/*7.12 刘艺杰新增
	 * 向订单表中插入单条数据
	 * 
	 */
	public boolean addOneOrder(Customers cus,Car car)
	{
		boolean flag = false;
		String sql = "insert into orderform (did ,cid , buy_date,good_num,gprice,gid,gname,picture,cname,tel,address) values (null, ? ,current_timestamp,?,?,?,?,?,?,?,?)";
		flag = JDBC_Util.updateDb(sql, cus.getCid(),car.getGood_num(),car.getGprice(),car.getGid(),car.getGname(),car.getPicture(),cus.getCname(),cus.getTel(),cus.getAddress());
	
	return flag;
	}
	
	/**
	 * 新增方法 向购物车里插入数据
	 *  7/9号
	 */
	public boolean addCar(int cid ,int gid, float gprice,int good_num,String picture,String gname) {
		String sql = "insert into shoppingcar (cid , gid, gprice, good_num,picture,gname) values (?,?,?,?,?,?)";
		boolean flag = JDBC_Util.updateDb(sql , cid  , gid , gprice , good_num ,picture,gname);
		return flag ; 
	}
	/*
	 *7.09 刘艺杰新增 
	 *  根据商品id和用户id联合查询购物车表，判断该用户是否已添加该商品进购物车	
	 * 
	 */
	public boolean checkCar(int cid,int gid) throws Exception {
		String sql = "select * from shoppingcar where cid = ? and gid = ?";
		ArrayList<Car> orderList = new 	ArrayList<Car>();
		orderList = (ArrayList<Car>)JDBC_Util.queryBeanList(sql, Car.class, cid,gid);
		if(orderList.size()>0)
			return true;
		return false;
	}
	
	/*7.09 刘艺杰新增
	 * 当用户已添加该商品进购物车时，将该商品的购买数量+1
	 * 
	 */
	public boolean updateCar(int cid,int gid) throws Exception {
		String sql = "update shoppingcar set GOOD_NUM = GOOD_NUM + 1 where cid = ? and gid = ?";
		boolean flag = false;
		flag = JDBC_Util.updateDb(sql, cid,gid);
		return flag ;
	}
	
	
	/*
	 * 7.09 刘艺杰新增
	 * 当用户提交订单时，对应商品的销量+1
	 */
	public boolean addSale(ArrayList<Car> carList) throws Exception{
		String sql = "update goods set sales = sales + 1 where gid = ?";
		boolean flag = true;
//		int i = 0;
		int cnt = 0;
		for(int i = 0;i<carList.size()&&flag;i++)
		{
			for(int j = 0;j<carList.get(i).getGood_num();j++)
			{
				flag = JDBC_Util.updateDb(sql, carList.get(i).getGid());
				cnt++;
			}
		}
		int sum = 0;
		for(int i = 0;i<carList.size();i++)
		{
			sum+=carList.get(i).getGood_num();
		}
		if(cnt == sum)
			return true;
		return false;
	}
	
	/*
	 * 7.10 刘艺杰新增
	 * 按照商品类型搜素商品
	 */
	public List<Goods> searchGoodsByGtype (int tid) throws Exception {
		String sql = "select * from goods where GTYPE = ?" ;
		List<Goods> gList = JDBC_Util.queryBeanList(sql, Goods.class,tid);
		return gList ;
	}
	
	/*7.10 刘艺杰新增
	 * 系统首页，按照商品名模糊搜索商品
	 * 
	 */
	public List<Goods> searchGoodsByGname (String gname) throws Exception {
		String sql = "select * from goods where GNAME like ?";
		gname="%"+gname+"%";
		List<Goods> gList = JDBC_Util.queryBeanList(sql, Goods.class,gname);
		for(int i = 0;i<gList.size();i++)
		{
			if(gList.get(i).getGtype().equals("101"))
			{
				gList.get(i).setGtype("服饰");
			}
			else if(gList.get(i).getGtype().equals("102"))
			{
				gList.get(i).setGtype("食品");
			}
			else if(gList.get(i).getGtype().equals("103"))
			{
				gList.get(i).setGtype("书籍");
			}
			else if(gList.get(i).getGtype().equals("104"))
			{
				gList.get(i).setGtype("科技");
			}
			else
			{
				gList.get(i).setGtype("日用");
			}
		}
		return gList ;
	}
	
	/*7.10 刘艺杰新增
	 * 系统首页，商品热销榜展示
	 * 按照商品销量从高到低排序
	 * 
	 */
	public List<Goods> orderGoodsBySales () throws Exception {
		String sql = "select * from goods order by sales desc limit 10";
		List<Goods> gList = JDBC_Util.queryBeanList(sql, Goods.class);
		return gList ;
	}
	
	
	/*7.10 刘艺杰新增
	 * 系统首页，商品热搜榜展示
	 * 按照商品搜索量从高到低排序
	 */
	public List<Goods> orderGoodsBySearch () throws Exception {
		String sql = "select * from goods order by searchnum desc limit 10";
		List<Goods> gList = JDBC_Util.queryBeanList(sql, Goods.class);
		return gList ;
	}
	
	
	/**7.10 安森泰新增  
	 * 首页：热搜表
	 * 当用户点击商品时进入详情页使数据库中的搜索量+1
	 */
	public boolean updateSearchNum(int gid) throws Exception {
		String sql = "update goods set SEARCHNUM = SEARCHNUM + 1 where gid = ?";
		boolean flag = false;
		flag = JDBC_Util.updateDb(sql,gid);
		return flag ;
	}
	
	
	/**7.10 刘艺杰新增
	 * 首页:随机推送表
	 * 从商品表中随机挑选几款商品推送到首页
	 */
	public List<Goods> orderGoodsByrandom(int number) throws Exception {
		String sql = "SELECT * FROM goods ORDER BY rand() LIMIT ?";
		List<Goods> gList = JDBC_Util.queryBeanList(sql, Goods.class,number);
		return gList;
	}
	
	/*7.11 卢晓丹
	 * 根据商品的gid查找商品
	 */
	public Goods getGoodsById(int gid) throws Exception {
		Goods goods = new Goods();
		String sql = "select * from goods where gid = ?";
		goods = JDBC_Util.queryBean(sql, Goods.class, gid);
		return goods;
	}
	
	
	/*7.11 卢晓丹
	 *按照店铺编号查找该商家的所有商品 
	 */
	public List<Goods> getGoodsByBid(String bid) throws Exception {
		String sql = "select * from goods where bid = ?";
		List<Goods> gList = JDBC_Util.queryBeanList(sql,Goods.class,bid);
		for(int i = 0;i<gList.size();i++)
		{
			if(gList.get(i).getGtype().equals("101"))
			{
				gList.get(i).setGtype("服饰");
			}
			else if(gList.get(i).getGtype().equals("102"))
			{
				gList.get(i).setGtype("食品");
			}
			else if(gList.get(i).getGtype().equals("103"))
			{
				gList.get(i).setGtype("书籍");
			}
			else if(gList.get(i).getGtype().equals("104"))
			{
				gList.get(i).setGtype("科技");
			}
			else
			{
				gList.get(i).setGtype("日用");
			}
		}
		return gList ;
	}	
	
	
	/*7.11 卢晓丹
	 *商家更新商品信息（名称 价格 类型 图片）
	 */
	public boolean updateGoods(Goods goods) throws Exception {
		String sql = "update goods set gname=?,gprice=?,gtype=?,picture=?,gdetail = ? where gid = ?" ;
		boolean flag = JDBC_Util.updateDb(sql,goods.getGname(),goods.getGprice(),goods.getGtype(),goods.getPicture(),goods.getGdetail(),goods.getGid());
		return flag; 
	}
	
	
	/*7.11 卢晓丹
	 * 	
	 */
	//删除商品
	public boolean deleteGoods(int gid) throws Exception {
		String sql = "delete from goods where gid = ? ";
		boolean flag = JDBC_Util.updateDb(sql,gid);//因为原函数的参数是动态参数，所以下方函数中参数不同
		return flag;
	}
	/*
	 * 7.11 卢晓丹
	 *		刘艺杰 改
	 */
	
	//新增商品
	public boolean addGoods(Goods goods) {
		String sql = "insert into goods values (null,?,?,?,now(),?,0,0,?,?,?)";
		//                   依次为gid,bid  gname,gprice uptime gtype  sales searchtime picture
		boolean flag = JDBC_Util.updateDb(sql,goods.getBid(),goods.getGname(),goods.getGprice(),goods.getGtype(),goods.getPicture(),goods.getGdetail(),goods.getGstore());
		return flag ;
	}

	
	/*7.11 刘艺杰 新增
	 * 买家进入商品详情页后
	 * 该用户对该商品类型的浏览量+1
	 */
	public boolean updateVisitNum(int cid,int tid) throws Exception {
		String sql = "update clike set VISIT_NUM = VISIT_NUM + 1 where cid = ? and tid = ?";
		boolean flag = false;
		flag = JDBC_Util.updateDb(sql,cid,tid);
		return flag ;
	}

	/*7.12 刘艺杰新增
	 * 更新用户的购物车中所有商品的数量
	 * 
	 */
	public boolean updateCarGoods(int good_num,int cid,int gid) throws Exception {
		String sql = "update shoppingcar set shoppingcar.GOOD_NUM = ? where cid = ? and gid = ?";
		boolean flag = false;
		flag = JDBC_Util.updateDb(sql,good_num,cid,gid);
		return flag ;
	}
	
	
	/*7.12 刘艺杰新增
	 * 新增购物车中某特定商品的销量，销量+=购物车中的数量
	 * 
	 */
	public boolean addOneGoodSale(int good_num,int gid) throws Exception {
		String sql = "update goods set SALES = SALES + ? where gid = ?";
		boolean flag = false;
		flag = JDBC_Util.updateDb(sql,good_num,gid);
		return flag ;
	}
	
	
	/*7.12 刘艺杰新增
	 * 新增购物车中某特定商品的销量,销量+=购物车中的数量
	 * 
	 */

	public boolean minusOneGoodStore(int good_num,int gid) throws Exception {
		String sql = "update goods set gstore = gstore - ? where gid = ?";
		boolean flag = false;
		flag = JDBC_Util.updateDb(sql,good_num,gid);
		return flag ;
	}
	
	
	/*7.12 刘艺杰新增
	 * 按照商品编号和用户id查找购物车中的商品对象
	 * 
	 */
	public Car getCarByCusAndGid(int cid,int gid) throws Exception{
		String sql = "select * from shoppingcar where cid = ? and gid = ?";
		Car car = new Car();
		car = JDBC_Util.queryBean(sql, Car.class, cid,gid);
		return car;
	}
	
	/*
	 * 7.12 卢晓丹新增
	 * 查看商家订单
	 */
	public List<Order> searchOrderByBid(String bid) throws Exception {
		String sql = "select o.did,o.cid,o.gid,o.gprice,o.buy_date,o.good_num,o.gname,o.picture,o.cname,o.tel,o.address from orderform o,goods where o.gid=goods.gid and goods.bid=? order by buy_date desc";
		List<Order> OrderList = JDBC_Util.queryBeanList(sql, Order.class, bid);
		return OrderList;
	}
	
	/*7.12 卢晓丹新增
	 * 根据订单中商品的gid查找某店铺的相关订单
	 * 
	 */
	public List<Order> getGoodsorderByGid(String bid,String gid) throws Exception {
		String sql = "select o.did,o.cid,o.gid,o.gprice,o.buy_date,o.good_num,o.gname,o.picture,o.cname,o.tel,o.address from orderform o,goods where o.gid=goods.gid and goods.bid=? and goods.gid=?";
		List<Order> OrderList = JDBC_Util.queryBeanList(sql, Order.class, bid, gid);
		return OrderList;
	}
	
	/*7.13 刘艺杰新增
	 * 根据商家ID和商品类型查询商品
	 * 
	 */
	public List<Goods> searchGoodsByBidAndTid (int bid,String tid) throws Exception {
		String sql = "select * from goods where  bid = ? and gtype = ?" ;
		List<Goods> gList = JDBC_Util.queryBeanList(sql, Goods.class,bid,tid);
		for(int i = 0;i<gList.size();i++)
		{
			if(gList.get(i).getGtype().equals("101"))
			{
				gList.get(i).setGtype("服饰");
			}
			else if(gList.get(i).getGtype().equals("102"))
			{
				gList.get(i).setGtype("食品");
			}
			else if(gList.get(i).getGtype().equals("103"))
			{
				gList.get(i).setGtype("书籍");
			}
			else if(gList.get(i).getGtype().equals("104"))
			{
				gList.get(i).setGtype("科技");
			}
			else
			{
				gList.get(i).setGtype("日用");
			}
		}
		return gList ;
	}
}
